/**
 * 
 */
package com.diy.excelsql.utils;


import com.diy.excelsql.common.CardType;
import com.diy.excelsql.common.Common;
import com.diy.excelsql.model.domain.TFhlCommonCardBinInfo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Hongten
 * @created 2014-5-18
 */
public class ReadExcel {

    // 生成20位 字符串
    public static final String STR00 = "00000000000000000000";
    public static final String STR99 = "99999999999999999999";
    public static final int STRINT = 19;


	public List<TFhlCommonCardBinInfo> readXlsfhl() throws IOException {
		InputStream is = new FileInputStream(Common.EXCEL_PATH_FHL);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
//		List<Student> list = new ArrayList<Student>();
		List<TFhlCommonCardBinInfo> list = new ArrayList<TFhlCommonCardBinInfo>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 4; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					TFhlCommonCardBinInfo tfhlCardBinInfo = new TFhlCommonCardBinInfo();

                    // 之后拆分 code 和 name
					HSSFCell cardIssuer = hssfRow.getCell(0);
					// 卡名
					HSSFCell cardName = hssfRow.getCell(1);
					// 主账号长度
					HSSFCell cardLength = hssfRow.getCell(8);
					// 发卡行标识
					HSSFCell cardBin = hssfRow.getCell(13);
					// 发卡行标识 长度
					HSSFCell cardBinLength = hssfRow.getCell(12);
					// 卡种,0借记卡 1贷记卡 2预付费卡 区别一下
					HSSFCell cardType = hssfRow.getCell(15);

					String carBinStr = getValue(cardBin);
					Integer cardBinLengthInt = Integer.valueOf(getValue(cardBinLength));
					// 计算卡号的 起始和结束范围
                    String cardStart = carBinStr + STR00.substring(0, STRINT-cardBinLengthInt);
                    String cardEnd = carBinStr + STR99.substring(0, STRINT-cardBinLengthInt);

                    String[] codeName = cardIssuerCodeAndName(getValue(cardIssuer));
                    tfhlCardBinInfo.setId((long) (rowNum-4));
                    tfhlCardBinInfo.setCardIssuerName(codeName[0]);
                    tfhlCardBinInfo.setCardIssuerCode(codeName[1]);
                    tfhlCardBinInfo.setCardName(getValue(cardName));
                    tfhlCardBinInfo.setCardLength(Integer.valueOf(getValue(cardLength)));
//                    tfhlCardBinInfo.setCardBin(getValue(cardBin));
                    tfhlCardBinInfo.setCardBin(carBinStr);
//                    tfhlCardBinInfo.setCardBinLength(Integer.valueOf(getValue(cardBinLength)));
                    tfhlCardBinInfo.setCardBinLength(cardBinLengthInt);
                    tfhlCardBinInfo.setCardType(Long.valueOf(CardType.getCodeByMsg(getValue(cardType))));
                    tfhlCardBinInfo.setCardStart(cardStart);
                    tfhlCardBinInfo.setCardEnd(cardEnd);
					list.add(tfhlCardBinInfo);
				}
			}
		}
		return list;
	}

	 @SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
	        if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
	            // 返回布尔类型的值
	            return String.valueOf(hssfCell.getBooleanCellValue());
//	        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
	        } else if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {
	            // 返回数值类型的值
	            return String.valueOf(hssfCell.getNumericCellValue());
	        } else {
	            // 返回字符串类型的值
	            return String.valueOf(hssfCell.getStringCellValue());
	        }
	    }

    /**
     * 获取 issuer 的 name 和 code
      * @param cardIssuer
     * @return
     */
    private String[] cardIssuerCodeAndName(String cardIssuer) {
	    // lines[0] name
	    // lines[1] code
        String lines[] = cardIssuer.split("\\r|\\n");
        lines[1] = lines[1].substring(lines[1].indexOf("(") + 1,lines[1].indexOf(")"));
        return lines;
    }
}
